package com.lei.springbootpractice.config;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

/**
 * 表名验证器
 * 用于检查和验证所有表名的一致性
 */
@RestController
@RequestMapping("/api/validate")
public class TableNameValidator {

    @Autowired
    private DataSource dataSource;

    /**
     * 验证所有表名和POJO映射的一致性
     */
    @GetMapping("/table-names")
    public Map<String, Object> validateTableNames() {
        Map<String, Object> result = new HashMap<>();
        Map<String, String> schemaTableNames = Map.of(
            "Category", "Category",
            "products", "products", 
            "t_user", "t_user",
            "Address", "Address",
            "Orders", "Orders",
            "cart", "cart",
            "ProductBunch", "ProductBunch",
            "review", "review",
            "console", "console",
            "emp", "emp"
        );
        
        Map<String, String> pojoTableNames = Map.of(
            "CategoryPojo", "Category",
            "ProductsPojo", "products",
            "UserPojo", "t_user", 
            "AddressPojo", "Address",
            "OrdersPojo", "Orders",
            "CartPojo", "cart",
            "ProductBunchPojo", "ProductBunch",
            "ReviewPojo", "review",
            "ConsolePojo", "console",
            "EmpPojo", "emp"
        );
        
        result.put("success", true);
        result.put("schemaTableNames", schemaTableNames);
        result.put("pojoMappings", pojoTableNames);
        result.put("status", "所有表名映射检查完成");
        
        return result;
    }

    /**
     * 验证特定表是否存在
     */
    @GetMapping("/verify-tables")
    public Map<String, Object> verifyTablesExist() {
        Map<String, Object> result = new HashMap<>();
        Map<String, Boolean> tableStatus = new HashMap<>();
        
        String[] expectedTables = {
            "Category", "products", "t_user", "Address", "Orders", 
            "cart", "ProductBunch", "review", "console", "emp"
        };
        
        try (Connection connection = dataSource.getConnection()) {
            for (String tableName : expectedTables) {
                boolean exists = checkTableExists(connection, tableName);
                tableStatus.put(tableName, exists);
            }
            
            result.put("success", true);
            result.put("tableStatus", tableStatus);
            
        } catch (SQLException e) {
            result.put("success", false);
            result.put("error", e.getMessage());
        }
        
        return result;
    }

    /**
     * 检查具体的查询语句是否能正常执行
     */
    @GetMapping("/test-queries")
    public Map<String, Object> testCriticalQueries() {
        Map<String, Object> result = new HashMap<>();
        Map<String, Object> queryResults = new HashMap<>();
        
        // 测试关键查询
        Map<String, String> testQueries = Map.of(
            "categoryParents", "SELECT COUNT(*) FROM Category WHERE Cat_category_id IS NULL",
            "categorySubcategories", "SELECT COUNT(*) FROM Category WHERE Cat_category_id IS NOT NULL",
            "productsCount", "SELECT COUNT(*) FROM products",
            "usersCount", "SELECT COUNT(*) FROM t_user",
            "joinQuery", "SELECT COUNT(*) FROM products p INNER JOIN Category c ON p.category_id = c.category_id"
        );
        
        try (Connection connection = dataSource.getConnection()) {
            for (Map.Entry<String, String> entry : testQueries.entrySet()) {
                try {
                    int count = executeCountQuery(connection, entry.getValue());
                    queryResults.put(entry.getKey(), Map.of(
                        "status", "success",
                        "count", count,
                        "query", entry.getValue()
                    ));
                } catch (SQLException e) {
                    queryResults.put(entry.getKey(), Map.of(
                        "status", "error",
                        "error", e.getMessage(),
                        "query", entry.getValue()
                    ));
                }
            }
            
            result.put("success", true);
            result.put("queryResults", queryResults);
            
        } catch (SQLException e) {
            result.put("success", false);
            result.put("error", e.getMessage());
        }
        
        return result;
    }

    private boolean checkTableExists(Connection connection, String tableName) throws SQLException {
        String sql = "SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ?";
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setString(1, tableName);
            try (ResultSet rs = stmt.executeQuery()) {
                return rs.next();
            }
        }
    }

    private int executeCountQuery(Connection connection, String sql) throws SQLException {
        try (PreparedStatement stmt = connection.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {
            if (rs.next()) {
                return rs.getInt(1);
            }
            return 0;
        }
    }
} 